Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).
Course Of The Month
3 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQLite Query needed
Posted on 2012-12-26
I'm struggling with figuring out a query that I think should be simple.
I have two tables, call them "rosters" and "lineups".
Lineups looks like this:
name, foreign_id1, foreign_id2, foreign_id3 ... foreign_id6
Where foreign_id1, foreign_id2, foreign_id3 ... foreign_id6 are all unique records in "rosters".
Example lineup records:
Example roster records:
I want the query to return a record for each row in lineups and use the foreign_ids (field crn) to get an associated item (dog_name) from the rosters table.
I have two tables, call them "rosters" and "lineups".
Lineups looks like this:
name, foreign_id1, foreign_id2, foreign_id3 ... foreign_id6
Where foreign_id1, foreign_id2, foreign_id3 ... foreign_id6 are all unique records in "rosters".
Example lineup records:
Example roster records:
I want the query to return a record for each row in lineups and use the foreign_ids (field crn) to get an associated item (dog_name) from the rosters table.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
-
4
10 Comments
Something like this should get you the records that you want. It will give you a record for each row in the lineup table and the name of each dog in the lineup from the roster table.
Select l.team_name, l.start, a.dog_name, l.dog_2, b.dog_name, l.dog_3, c.dog_name, l.anchor, d.dog_name
From lineups l
Inner Join roster a on a.crn = l.start
inner join roster b on b.crn = l.dog_2
inner join roster c on c.crn = l.dog_3
inner join roster d on d.crn = l.anchor
Select l.team_name, l.start, a.dog_name, l.dog_2, b.dog_name, l.dog_3, c.dog_name, l.anchor, d.dog_name
From lineups l
Inner Join roster a on a.crn = l.start
inner join roster b on b.crn = l.dog_2
inner join roster c on c.crn = l.dog_3
inner join roster d on d.crn = l.anchor
Active today
Your example doesn't seem to show any crns in the rosters table that match any foreignids in the lineups table. Perhaps you can post some actual sample data and your expected results.
Jmiller, this is very close to what I need. It works perfectly for getting start, dog_2, dog_3 and anchor.
But, some records in lineups will have NULLS for empty strings for bench_1 and bench_2. When I changed your query to this:
I only get the records where bench_1 and bench_2 actually exist in club_rosters.
I can control what goes into the bench_1 and bench_2 fields when there are only valid entries for start, dog_2, dog_3, and anchor. Or, maybe I should just put a record in the club_rosters table that has a NULL crn?
But, some records in lineups will have NULLS for empty strings for bench_1 and bench_2. When I changed your query to this:
Select l.team_name, a.dog_name as start, b.dog_name as dog_2, c.dog_name as dog_3, d.dog_name as anchor, e.dog_name as bench_1, f.dog_name as bench_2
From lineups l
Inner Join club_rosters a on a.crn = l.start
inner join club_rosters b on b.crn = l.dog_2
inner join club_rosters c on c.crn = l.dog_3
inner join club_rosters d on d.crn = l.anchor
inner join club_rosters e on e.crn = l.bench_1
inner join club_rosters f on f.crn = l.bench_2
I only get the records where bench_1 and bench_2 actually exist in club_rosters.
I can control what goes into the bench_1 and bench_2 fields when there are only valid entries for start, dog_2, dog_3, and anchor. Or, maybe I should just put a record in the club_rosters table that has a NULL crn?
Active today
My instincts tell me that it may not be necessary to have 6 joins, but without any data with which to taste it is difficult to verify. Is the data too sensitive to actually post? If so, could you provide some made up data and the expected results?
Here's an example set of records from table lineups:
Here's a sample of result almost like what I need (the bench_1 and bench_2 fields have crn from lineups table instead of dog_name from club_rosters. Adding inner joins for these last two fields eliminates records where those fields are null or empty in lineups
There was an example of club_rosters records in the original question. The only thing relevant about that table is that a record will exist with a matching "crn" field, for every non-null (or empty) value of start, dog_2, dog_3, anchor, bench_1 and bench_2.
start, dog_2, dog_3, and anchor will always be non-null and will consist of numeric data as char(6). bench_1 and bench_2 are also char(6), but may be NULL or empty.
records-desired.png
Here's a sample of result almost like what I need (the bench_1 and bench_2 fields have crn from lineups table instead of dog_name from club_rosters. Adding inner joins for these last two fields eliminates records where those fields are null or empty in lineups
There was an example of club_rosters records in the original question. The only thing relevant about that table is that a record will exist with a matching "crn" field, for every non-null (or empty) value of start, dog_2, dog_3, anchor, bench_1 and bench_2.
start, dog_2, dog_3, and anchor will always be non-null and will consist of numeric data as char(6). bench_1 and bench_2 are also char(6), but may be NULL or empty.
records-desired.png
Active today
>>There was an example of club_rosters records in the original question<<
Yes, but there were no crns that matched any of the start, dog_2, dog_3, anchor, bench_1, or bench_2 records in the lineups table. So where did names like Clyde, Dusty, Blade, Blackjack, etc. come from? I can only assume they were in the rosters table with crns matching one of the attributes in the lineups table. It is that matching data I would like to see.
Yes, but there were no crns that matched any of the start, dog_2, dog_3, anchor, bench_1, or bench_2 records in the lineups table. So where did names like Clyde, Dusty, Blade, Blackjack, etc. come from? I can only assume they were in the rosters table with crns matching one of the attributes in the lineups table. It is that matching data I would like to see.
I can only assume they were in the rosters table with crns matching one of the attributes in the lineups table. It is that matching data I would like to see.
Yes, that is correct. I just showed a few records from the club_rosters table (there are thousands).
All that's relevant is that there will be a record with a matching crn in the club_rosters table for each non-null, non-empty field of the line-ups. If you need example data, just use the dog names from the desired results and crns from the lineups query.
Slight modification to jmiller's query (lefgt outer joins) gives what I need:
I'll award the points to jmiller unless awking00 wants to keep trying for a simpler query. If awking00 comes up with one, I'll split the points 70%/30% since he seems to be actively working on it.
Thanks,
Dave Thomas
Select l.team_name, a.dog_name as start, b.dog_name as dog_2, c.dog_name as dog_3, d.dog_name as anchor, e.dog_name, f.dog_name
From lineups l
left outer Join club_rosters a on a.crn = l.start
left outer join club_rosters b on b.crn = l.dog_2
left outer join club_rosters c on c.crn = l.dog_3
left outer join club_rosters d on d.crn = l.anchor
left outer join club_rosters e on e.crn = l.bench_1
left outer join club_rosters f on f.crn = l.bench_2
I'll award the points to jmiller unless awking00 wants to keep trying for a simpler query. If awking00 comes up with one, I'll split the points 70%/30% since he seems to be actively working on it.
Thanks,
Dave Thomas
Active today
Don't know whether or not it's "simpler" but should perform better than 6 hash joins and 7 full table scans -
select l.team_name
,max(case when l.start = c.crn then c.dog_name end) start
,max(case when l.dog_2 = c.crn then c.dog_name end) dog_2
,max(case when l.dog_3 = c.crn then c.dog_name end) dog_3
,max(case when l.anchor = c.crn then c.dog_name end) anchor
,max(case when l.bench_1 = c.crn then c.dog_name end) bench_1
,max(case when l.bench_2 = c.crn then c.dog_name end) bench_2
from lineups l,
club_rosters c
where c.crn = l.start
or c.crn = l.dog_2
or c.crn = l.dog_3
or c.crn = l.anchor
or c.crn = l.bench_1
or c.crn = l.bench_2
group by l.team_name;
select l.team_name
,max(case when l.start = c.crn then c.dog_name end) start
,max(case when l.dog_2 = c.crn then c.dog_name end) dog_2
,max(case when l.dog_3 = c.crn then c.dog_name end) dog_3
,max(case when l.anchor = c.crn then c.dog_name end) anchor
,max(case when l.bench_1 = c.crn then c.dog_name end) bench_1
,max(case when l.bench_2 = c.crn then c.dog_name end) bench_2
from lineups l,
club_rosters c
where c.crn = l.start
or c.crn = l.dog_2
or c.crn = l.dog_3
or c.crn = l.anchor
or c.crn = l.bench_1
or c.crn = l.bench_2
group by l.team_name;
Featured Post
Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have.
In this…
Suggested Courses
Course of the Month3 days, 9 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
691 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.