SQL Not Working As Expected [SETTLED]

Development Web Development
,
1

I'm currently running two SQL queries, one is very simple, all it does is return the titles of the products. I've made an application which will look through all products and only gather a select few depending on what the user has searched for. So the SQL is like so:

SELECT Title FROM Table1

However, in order for the products to have a link assigned to them, so the uesr an look at each product individually, I need another query, which looks at another two different tables, so the query I'm running there is like so:

SELECT Table1.PID,
       Table1.Title,
       Table2.PID,
       Table2.FID,
       Table3.FID,
       Table3.URL

 FROM Table1, Table2, Table3

 WHERE Table1.PID = Table2.PID 
 AND   Table2.FID = Table3.FID

Now if I were doing this correctly, then both queries would return the same number of results. However, sadly, I'm clearly doing something wrong as there are roughly 200 products returned that do not have a URL assigned to them.

I'm assuming there's something wrong with the logic I'm using here, and if anyone tells me to change the structure of the DB, I have not got access to change the DB, only access to query the DB.

FYI. I know that the URL and the Title aren't being used in the query, but that's the data I'm trying to output.

Any help would be much obliged. I wouldn't be posting it here if I wasn't stuck, I've tried a lot of different things, and sadly there has been no luck as of yet. I've been stuck on this issue for a few days now, so yeah, it's probably that I'm a total noob with SQL, or so I'm assuming.

I've also tried to include the LIKE operator in there, but at the moment, it's too specific, and isn't returning enough data for my liking OR for the needs of the website.

In the second query, I just tried to include:

AND Table1.Title LIKE '%search%'
2

What about using a JOIN statement?

SELECT * FROM (
    SELECT
            a.PID,
            a.Title,
            b.PID,
            b.FID,
            c.FID,
            c.URL
	FROM 
		Table1 as a
	LEFT JOIN 
		Table2 as b
	ON
		a.PID = b.PID
	LEFT JOIN
		Table3 as c
	ON 
		b.FID = c.FID
) as resultSet;

You can then use the resultSet to work on that set further.

3

The JOIN statement seemed to have fixed it kind, BUT I think there's actually a structure issue with the database as some of the URL's are just empty, I mean their and empty string.

HOWEVER I'd like to thank you for that help, I mean it kinda works better in the respect that if the URL's weren't empty strings, then that would work perfectly.

1 Like
4

What are you using MsSQL, MySQL, TSQL?

If you can, make a diagram of your database relationships and I'll give you some tips.

5

SQL Server. As for the database relationships, I haven't got access to that, long story short, the company I work for have bought out this company. This company's website that I'm currently working on, I didn't even know what tables were in the DB until I made a query to test it. 

SELECT Name FROM sys.Tables

The company that sold all the data/rights, etc, they're being impossible to work with, and the site was a total mess, both frontend and backend. I have no idea why, but they made it all static, a static website with at least 100 different pages, why you'd do that I have 0 idea. They didn't even have a somewhat or even slightly responsive layout, it was originally awful. I mean it's far from perfect now, but I can certainly say it's already better.

The previous developers just won't give us anything, not even access to change the DB, it's actually ridiculous.

6

I also just ran another query to test if it's the logic I'm using or if something's up with the database itself. I can confirm that it's the database itself, there is data missing from certain tables. I just ran a query to retrieve all of the PID's from Table1 and a separate query to get all the PID's from Table2.

Obviously they should return the same results. Well they didn't. There were some PID's that existed in Table1, but not in Table2. Obviously that f**ks up the original query.

7

Oh dear....

Seems like you've got quite the task ahead of you in fixing that mess. I myself would duplicate the entire DB and fix/patch what I can on a local machine before resyncing changes and then replacing the DB with the fixed version.

Maybe also document what you find. This will probably not be the last you see of a messy DB. :wink:

8

Dear lord, I've only been working as a Junior Developer for nearly 2 months, please don't tell me there's a lot more pain to come? :joy:

Luckily, the front end is good enough for now, my boss is more than happy with the front end, and the back end work that I HAVE been able to do, he's also very happy with that. He's actually seriously impressed with a searching algorithm that I made, it's not the quickest, simply because it tries to return as much data as possible, and it handles all kinds of errors from typos to invalid characters (trying to stop XSS) to products that have a similar name to the one that the user searched. Even though it's not the quickest, it's still not what I'd call slow, by any means! :wink:

I'm actually really happy with that algorithm myself, it's not too bad for a junior's work IMO.

1 Like
9

Try FlySpeed: http://www.activedbsoft.com/overview-querytool.html

This has helped me immensely in seeing what's in the DB and setting up queries.

10

It sounds like a useful product, but I'm already aware as to what's in the database, just from running so many tests. And setting up queries, I don't really tend to do anything too complex.

On a different topic... @catsay As for that algorithm, I was just working on it, trying to make it a little faster, and I've decided to sacrifice on the amount of results that can be returned. I mean if you typed in data that was vague enough, it could easily return over 500 different products, if not 1,000+. Well I've capped it to 100 results, including that cap has made it insanely fast. I mean realistically, who's gonna look through 500~1,000+ different results?

I mean I made a global variable for the limit, so if I ever wanna allow more results, I can just change the variable's value! :stuck_out_tongue_winking_eye:

11

Could just make the Limit range based and add pagination to it.

So you just use Limit 0, 100 on the first page, Limit 100, 200 on the next and so on. Should cover everything.

12

That's the plan at least! ... I was just messing around just to see what could be improved. :slight_smile:

13

Booya! Capping on the database side is the way to go about this. I worked for a company that made a pretty irritating product. You could get a print preview of your data on the forms that it created, and you could set a limit for how many forms it would preview. But the limit option didn't set a LIMIT on the query. No matter what the user did, the preview would always grab all of the records from the database, and then the application would drop all of the results beyond the number specified in the limit. This became particularly problematic when they signed on customers who would have tens of millions of records. Talk about completely fscking up your database cache.

14

I like that...

Touché my friend, touché.