Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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==-
Copy link to clipboard
Copied
You can use the group attribute of cfoutput query="yourquery" to get the gallery names to display just once. You have a problem with your database design though. Storing the clientid in the gallery table is forcing you to have duplicate records.
Copy link to clipboard
Copied
Storing the clientid in the gallery table is
forcing you to have duplicate records.
Not necessarily. How are you defining unique gallery names? Unique galleries by client, or just unique names period.
You can use the group attribute of cfoutput
query="yourquery" to get the gallery names to display just
once.
Generally it is better to retrieve only the data needed, rather than grabbing everything, but only outputting a subset of the records. Granted if the resultset is small, performance differences may not be significant.
Copy link to clipboard
Copied
Yes you are right I have not got duplicate records As I am displaying galleries by ClientID, Sub galleries are then displayed if the gallery has sub galleries.
This code you posted looks like what I am after
SELECT LUGallery.GalleryID,
LUGallery.GalleryName
FROM LUGallery
WHERE EXISTS (
SELECT *
FROM LUSubGallery
WHERE LUGallery.GalleryID = LUSubGallery.GalleryID
)
but I get the following errror.
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'LUSubGallery'.
Thanks for trying to help me.
Copy link to clipboard
Copied
but I get the following errror.
It looks okay. Though I did not test it. Try running the statement
directly in your database. It usually provides better error messages.
Copy link to clipboard
Copied
but I get the following errror.
It looks okay. Though I did not test it. Try running the statement
directly in your database. It usually provides better error messages.
It runs fine for me.
So I don't think that is the entirety of the <cfquery> being run.
Can you post the actual code.
--
Adam
Copy link to clipboard
Copied
HI This is the query that is causing the error in coldfusion
<cfquery name="exists" datasource="#Application.dsn#">
SELECT LUGallery.GalleryID, LUGallery.GalleryName
FROM LUGallery
WHERE EXISTS (
SELECT *
FROM LUSubGallery
WHERE LUGallery.GalleryID = LUSubGallery.GalleryID
)
LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID
</cfquery>
Also I get this error
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'LUSubGallery'.
when I run The query
SELECT LUGallery.GalleryID, LUGallery.GalleryName
FROM LUGallery
WHERE EXISTS (
SELECT *
FROM LUSubGallery
WHERE LUGallery.GalleryID = LUSubGallery.GalleryID
)
LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID
In SQL Server Management Studio Express
Copy link to clipboard
Copied
HI This is the query that is causing the error in
coldfusion
Try running the query you posted earlier. Notice, it does not have the extra "LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID" statement at the end. That is what is causing the error. Remove it and it should work properly.
SELECT LUGallery.GalleryID, LUGallery.GalleryName
FROM LUGallery
WHERE EXISTS (
SELECT *
FROM LUSubGallery
WHERE LUGallery.GalleryID = LUSubGallery.GalleryID
)
-Leigh
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
That query will only return galleries that have sub-galleries. This may or may not be what you wanted.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
I believe what you are suggesting is the opposite of what the OP is after ..
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
I dont see how its the opposite, considering it fits his simplified description of what he is trying to do.
My bad. That is what happens when you skim ...
... just simplier
Personally, I do not find it is simpler. Just a slightly different way of writing it. But yes, both options are perfectly valid.
... and it should also be faster.
Why do you say that?
Copy link to clipboard
Copied
... and it should also be faster.Why do you say that?
Experience has shown me that it is faster in cases I have had in the past on various db's. There are also quite a few blog entries around that discuss the subject; for and against i must admit.
However, I am using MS SQL 2005 at the moment and tried out a demo of the two different query types. What is interesting, is that the execution plan is exactly the same. So the query optimiser seems to be doing its job, and it wouldn't matter what way it was done.
I think the performance though of in vs exists will also vary from engine to engine, so people might experience different results because to this. It is also important to note the exclusion using "not in vs not exists vs left outer join" is another kettle of fish.
Thanks for asking the question though, otherwise i wouldn't of even bothered looking into it again.
Cheers
Copy link to clipboard
Copied
I think the performance though of in vs exists will also
vary from engine to engine, so people might experience
different results because to this.
Yes, fair point. How well statements are optimized depends a lot on the database. On average, MS SQL tends to do a pretty good job of divining the intent of similar statements, and reducing them to the same execution plan. But not every database will behave the same. So reviewing the execution plans is probably the best approach.