Solved

How would I write this SELECT statement?

Posted on 2016-08-23
6
66 Views
Last Modified: 2016-08-24
Here's what I've got in my table right now. Take note of the DISTINCT door_id's (389, 287)

shopping cart
I've got another table called doors. Each door id has a corresponding "feature_door_name." I need to write a SELECT statement that starts off with something like this:

select DISTINCT door_id from cart where session_id='i2rnvaoealphebajh9e0jsipb7'

..and do a JOIN of some kind that allows me to retrieve the feature_door_name. That data looks like this:

table
What do you think?
0
Comment
Question by:brucegust
6 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 41767984
Either INNER or LEFT JOIN, based on your requirement.

SELECT t1.*, c.feature_door_name
   FROM your_table t1
   JOIN cart c ON t1.door_id = c.door_id

Open in new window


SELECT t1.*, c.feature_door_name
   FROM your_table t1
   LEFT JOIN cart c ON t1.door_id = c.door_id

Open in new window

0
 

Author Comment

by:brucegust
ID: 41767986
When I do this:

select distinct c.door_id, d.feature_door_name from cart c INNER JOIN doors d on c.door_id=d.id where c.session_id='i2rnvaoealphebajh9e0jsipb7'

I expect to get to "featurenames" based on the fact that I've got two distinct door_ids in the cart, but I only get one.
0
 

Author Comment

by:brucegust
ID: 41767991
This:

SELECT c.*, d.feature_door_name FROM doors d LEFT JOIN cart c ON d.id = c.door_id and c.session_id='i2rnvaoealphebajh9e0jsipb7'

...gives me this:

too many
I'm missing something. I need to see:

Brandywine Shaker
Ginger Deluxe

..that's it.

What have I got to do?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 50

Accepted Solution

by:
Ryan Chong earned 250 total points
ID: 41768179
try this
SELECT c.door_id , d.feature_door_name 
FROM cart c LEFT JOIN doors d ON d.id = c.door_id 
Where c.session_id='i2rnvaoealphebajh9e0jsipb7' 
group by c.door_id , d.feature_door_name

Open in new window

0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 200 total points
ID: 41768416
try other way around for LEFT JOIN.
SELECT c.* , d.feature_door_name 
FROM cart c LEFT JOIN doors d ON d.id = c.door_id 
Where c.session_id='i2rnvaoealphebajh9e0jsipb7' 

Open in new window

0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 50 total points
ID: 41768485
Since you start with the doors, you get all doors. Depsite of selecting all door fields as right fields in your field list, it's the "left" table the join starts from, therefore all the NULL rows for doors not having a cart entry. Simply start any query from the main table you want to query and join side data to this, not the other way around.

You might do the RIGHT JOIN instead, but I have to go far back to look where I ever needed this, it's typically only needed in conjunction with at least three tables, as you can of course always switch the order of two tables to make the join a left join. What matters is the order in which tables  are joined in the query, not the orientation of columns in the result.

Bye, Olaf.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question