Participating Frequently

Question

Making a join seems simple but I can't get it to work

Forum|Forum|16 years ago
February 1, 2010
3 replies
1693 views

Hi All,

I have 2 tables LUGallery and LUSubGallery, the tables are related by the GalleyID field

LUGallery

Gallery ID	ClientID	GalleryName
5	50	Australia
8	50	Weddings
12	33	Portraits
4	33	Landscapes

LUSubGallery

SubGalleryID	GalleryID	GalleryName
67	5	NSW
68	5	QLD
69	8	Reception
70	8	Location
87	8	Ceromony
97	4	Rain Forest

What I am try to do seems simple but I can't get it to work the way I want it.

I am trying to write a query to display Galleries from the LUGallery tbl that have a Sub Gallery attached to them (ie. It's GalleryID appears in the LUSubGallery tbl)

So the query for the above example would list Australia, Weddings & Landscapes

I have tried to write code both with inner joins and nested queries but just can't get it right,

Any help would be greatly appreciated.

Thanks in advance

Kris

Advanced techniques

This topic has been closed for replies.

A

Anonymous

Why even use the EXISTS clause at all? I am pretty sure this only runs a join type query underneath the covers anyway.

Much simplier would be:

select * from LUGallery

where GalleryID in (select GalleryID from LUSubGallery)

You should pretty much only use JOINS if you want columns from both tables, other wise like above just use IN. Also dont use select * like I have in production code.

Cheers

-

-__cfSearching__-

Inspiring

I believe what you are suggesting is the opposite of what the OP is after ..

A

Anonymous

I dont see how its the opposite, considering it fits his simplified description of what he is trying to do. It is also doing the same thing as your last suggested sql statement, just simplier and it should also be faster.

Maybe he will get back to us and let is know.

Cheers

W

whereskrisAuthor

Participating Frequently

Thanks to everyone for their input I have got a soloution, I'm not sure if it is the best or not but it works and it is a combination of a join and group attribute for cfquery.

<cfquery name="rsGetGalleriesWithSubGalleries" datasource="#Application.dsn#">
SELECT LUGallery.GalleryID, LUGallery.GalleryName
FROM LUGallery
INNER JOIN LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID
WHERE ClientID = '#Application.ClientID#'
</cfquery>

<cfoutput query="rsGetGalleriesWithSubGalleries" group="GalleryName">
#rsGetGalleriesWithSubGalleries.GalleryName#<br>
</cfoutput>

Thanks for your input.

Kris

D

Dan_Bracuk

Inspiring

That query will only return galleries that have sub-galleries. This may or may not be what you wanted.

W

whereskrisAuthor

Participating Frequently

Yes this is exactly what I was after

I am trying to write a query to display Galleries from the LUGallery tbl that have a Sub Gallery attached to them (ie. It's GalleryID appears in the LUSubGallery tbl)

Maybe I could have said it a bit simpler.

-

-__cfSearching__-

Inspiring

I have tried to write code both with inner joins and nested
queries but just can't get it right,

Can you post your query?

W

whereskrisAuthor

Participating Frequently

I have tried this join

<cfquery name="join" datasource="#Application.dsn#">
SELECT     LUGallery.GalleryID, LUGallery.ClientID, LUGallery.GalleryName, LUGallery.Rating, LUGallery.GalleryImage, LUGallery.GalleryInformation,
                      LUGallery.DisplayOnWebsite, LUGallery.DisplayType, LUGallery.Username, LUGallery.Password, LUGallery.GalleryDateAdded,
                      LUGallery.GalleryDateEdited, LUGallery.DirName, LUGallery.Locked, LUSubGallery.SubGalleryID, LUSubGallery.GalleryID AS Expr1,
                      LUSubGallery.GalleryName AS Expr2, LUSubGallery.Rating AS Expr3, LUSubGallery.GalleryImage AS Expr4,
                      LUSubGallery.GalleryInformation AS Expr5, LUSubGallery.DisplayOnWebsite AS Expr6, LUSubGallery.DisplayType AS Expr7,
                      LUSubGallery.GalleryDateAdded AS Expr8, LUSubGallery.GalleryDateEdited AS Expr9
FROM         LUGallery INNER JOIN
                      LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID
</cfquery>

It returns all of the records from both tables that Have the same GalleryID, this is no good to me as I just want to display the GalleryNames from the LUGallery tbl once

Kris

-

-__cfSearching__-

Inspiring

I just want to
display the GalleryNames from the LUGallery tbl once

Then you probably do not want include all the extra columns from the LUSubGallery table.

It returns all of the records from both tables that Have
the same GalleryID

That is the way this type of JOIN works. It will return one record for each matched GalleryID. If you only want to display the unique gallery names you can either:

1) Use an EXISTS clause. It will return the distinct records from the main gallery table IF a matching record exists in the LUSubGallery table

--- Not tested ---

SELECT LUGallery.GalleryID,

LUGallery.GalleryName

FROM LUGallery

WHERE EXISTS (

SELECT *

FROM LUSubGallery

WHERE LUGallery.GalleryID = LUSubGallery.GalleryID

)

http://www.techonthenet.com/sql/exists.php

... OR ....

2) Use the DISTINCT operator to return only the unique combinations of the selected columns. Note: DISTINCT considers all columns in the SELECT list . So if you only want unique galleries, do not include the LUSubGallery columns in the SELECT list.

http://www.w3schools.com/SQl/sql_distinct.asp

Message was edited by: -==cfSearching==-

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded