SQL: How to get list pairs of table
sql a b b a
Requirement: List the customer pairs who frequent the same bar (i.e., list all the pairs of customer who might meet in a coffee bar)
------------------------- id| name ------------------------- 1 | Vivamus nibh 2 | odio tristique 3 | vulputate ullamcorper 4 | Cras lorem 5 | libero est,
----------------------- id| name ----------------------- 1 | Warren 2 | Olympia 3 | Logan 4 | Summer 5 | Kamal 6 | Fernandez
----------------- cust_id | bar_id ----------------- 1 | 1 2 | 1 3 | 2 4 | 2 5 | 3 6 | 4
--------------------------------------- customer1 | customer2 | barname --------------------------------------- Warren | Olympia | Vivamus nibh Logan | Summer | odio tristique
Here is my try, but it didn't work:
select c1.name, c2.name, b1.name, b2.name from frequents f1, frequents f2 join bar b1 on f1.bar_id = b1.id join bar b2 on f2.bar_id = b2.id join customer c1 on f1.cust_id = c1.id join customer c2 on f2.cust_id = c2.id where f1.bar_id = f2.bar_id;
You could join the bar table with the frequent table, twice, and then continue joining to get the customers name. In order to prevent duplicates, you could take an arbitrary decision that one
cust_id should be smaller than the other:
SELECT b.name, c1.name, c2.name FROM bar b JOIN frequents f1 ON f1.bar_id = b.id JOIN frequents f2 ON f2.bar_id = b.id AND f1.cust_id < f2.cust_id JOIN customer c1 ON c1.id = f1.cust_id JOIN customer c2 ON c2.id = f2.cust_id
Query for pair-wise relationships between rows for a SQL Server table, How do I show relationships between pairs of rows in a table where the For example, PersonID values 5 and 6, both have GroupID B. These To get all pairs per bar, self-joining the Frequents table can get you that. Then you can just join the other tables on their ID to get the names.
I am using a sub query to join on frequents with same bar but different customer and also making use of "sorting" customer id by using > to avoid duplicates
SELECT c1.name customer1, f2.name customer2, b.name barname FROM frequents f JOIN customer c1 ON c1.id = f.cust_id JOIN bar b ON f.bar_id = b.id JOIN (SELECT cust_id, bar_id, name FROM frequents JOIN customer ON id = cust_id) AS f2 ON f2.bar_id = f.bar_id AND f2.cust_id > f.cust_id
Select pairs (a,b) excluding (b,a) with same value, Select pairs (a,b) excluding (b,a) with same value · sql-server. For a given structure like this: Get the list of pairs (ID, ID2) with same Name, excluding all records where (ID2, CREATE TABLE Pairs(Id varchar(5), Name varchar(20)); INSERT INTO Pairs SELECT p1.Id,p2.Id,p1.Name FROM Pairs p1 JOIN Pairs p2 ON p1. In addition to a list of values, you can use a subquery that returns a list of values with the IN operator as shown below: column | expression IN (subquery) In this syntax, the subquery is a SELECT statement that returns a list of values of a single column. Note that if a list contains NULL, the result of IN or NOT IN will be UNKNOWN.
To get all pairs per bar, self-joining the Frequents table can get you that.
Then you can just join the other tables on their ID to get the names.
SELECT cust1.name AS customer1, cust2.name AS customer2, bar.name AS barname FROM frequents freq1 JOIN frequents freq2 ON (freq2.bar_id = freq1.bar_id AND freq2.cust_id > freq1.cust_id) JOIN bar ON bar.id = freq1.bar_id LEFT JOIN customer cust1 ON cust1.id = freq1.cust_id LEFT JOIN customer cust2 ON cust2.id = freq2.cust_id ORDER BY freq1.cust_id, freq2.cust_id, freq1.bar_id;
SqlFiddle test here
SQL Pair Query?, SQLite/SQL Server don't have the LEAST() or GREATEST() functions. Or, a CTE (Common Table Function - also available Origin ) ) as List( Element ) ) as Destination from Routes ) select count(0) as TotalJourneys, Origin, Get Column Names From Table Example 2. This example explains how to find the list of Column names in a Table using sys.columns.-- Query to Get Column Names From Table in SQL Server USE [SQL Tutorial] GO SELECT name FROM sys.columns WHERE OBJECT_ID = OBJECT_ID('NewCustomers') OUTPUT
Information Modeling and Relational Databases, row from the table and is sometimes called a “correlation variable”, “range variable”, gender of various scientists, and the query lists pairs of scientists of opposite gender. Some versions of SQL also provide a “create synonym” command for Curie, Marie F Curie, Pierre M Edison, Thomas M Lovelace, Ada F select S1. In later versions of SQL Server, T-SQL works with JSON, so this is an additional option if you're using a later edition of SQL Server. Solution. Key value pairs provided a useful tool to map data to each other, such as mapping a public key to a private key, or mapping items to an object - like city names within a country.
Name Value Pairs Table Handling – SQLServerCentral, Transforming the Name-Value data into SQL tables For each pair, we select against the pair name and extract the pair value into a variable. Re: How to list all PRIMARY KEYS, FOREIGN KEYS and INDEXES of a TABLE ? 626608 Jul 15, 2008 6:49 PM ( in response to Sven W. ) yep, for me, I don't have access to dba tables.
Solved: Obtain all possible pairs from a list, proc sql; create table want as select distinct a.element,b.element as elementb from have as a, have as b where a.element<b.element; quit;. Here is a nice little SQL function that could help you extract key value pairs from a string. This can be really helpful in parsing log statements/audit logs stored in SQL servers. This can be really helpful in parsing log statements/audit logs stored in SQL servers.
- See meta.stackoverflow.com/questions/333952/…
- It is not clear what a pair is here, like the previous comment point out we need sample data and expected result
- @JoakimDanielson Thanks for reminding me. I've edited.
- @PhuongThuan both the requirements look similar. If they are different, you need to highlight the difference with two different expected outputs. Also, your sample data is not enough, as it should contain some customers which may not pair. Currently, all the customers and bar are making a possible pair in expected output.
- @MadhurBhaiya I've edited. Thanks