SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Beelzebozo posted:

I normally recommend against separately storing an end_date in cases where a set of date ranges should form a full cover, such as an audit of a status change. Not because of any storage redundancy or performance concern, but that just means that humans have to maintain the full cover property and opens up the possibility of somebody creating a situation where there are overlapping ranges or parts of the time series not covered by any range, which creates an unknown semantics scenario that software or reports might not expect.

That was my thought as well; it's highly unlikely but possible that the end date of one record and the start date of the next record become out of sync, or what not.

This is being used to store iterations of account information, so there will never be any overlap and never any gaps. Make a change, a new record gets inserted, maintaining a solid audit trail.

# ? Feb 7, 2013 18:57

Adbot: ADBOT LOVES YOU

# ? May 30, 2024 11:34

Beelzebozo: Nov 6, 2002; I don’t mean to sound bitter, cold, or cruel. But I am, so that’s how it comes out.

Golbez posted:

there will never be any overlap and never any gaps

You have no need to store an end_date. The end_date for each row can be presumed to be the day prior to the begin_date of the next row, or "current" if there is not a next row by date for that key.

# ? Feb 7, 2013 19:11

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

fletcher posted:

code:
SELECT DISTINCT SomeColumn FROM SomeTable ORDER BY SomeColumn
Returns in 0.03s w/ 164 rows
code:
SELECT COUNT(*) FROM SomeTable WHERE SomeColumn = 'Particular Value'
Returns in 1.89s w/ a count of ~100k
code:
SELECT SomeColumn, COUNT(*) FROM SomeTable GROUP BY SomeColumn
Goes out to lunch, still nothing after 25 minutes

SomeTable is ~300m rows. If it can do query #2 so quick, why it is having a hard time with query #3?

Also, there's an index on SomeColumn, but does it make sense to have one with so few distinct values? I guess it certainly helps query #1 perform better.

Just to follow up on this, increasing myisam_max_sort_file_size seems to have fixed my issue. Hooray!

# ? Feb 7, 2013 20:03

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Beelzebozo posted:

You have no need to store an end_date. The end_date for each row can be presumed to be the day prior to the begin_date of the next row, or "current" if there is not a next row by date for that key.

Is there an easy way, then, to get the range for a particular row in one query?

# ? Feb 7, 2013 20:38

Pardot: Jul 25, 2001

Golbez posted:

Is there an easy way, then, to get the range for a particular row in one query?

Check out window functions, you can use them to easily roll up the next record.

# ? Feb 7, 2013 20:45

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Pardot posted:

Check out window functions, you can use them to easily roll up the next record.

What are my options within MySQL?

# ? Feb 7, 2013 20:54

Beelzebozo: Nov 6, 2002; I don’t mean to sound bitter, cold, or cruel. But I am, so that’s how it comes out.

Yeah, sorry, I'm a postgres DBA, and I would use window functions. They've been in the standard since 2003. I have no idea how to do anything in MySQL that isn't standard SQL, I'm afraid.

# ? Feb 7, 2013 20:59

Pardot: Jul 25, 2001

Golbez posted:

What are my options within MySQL?

Do yourself a favor and migrate :shobon:

I honestly have no idea. If people would mention that they're unfortunately not on postgres I can stop answering peoples questions with features they don't have access to

# ? Feb 7, 2013 20:59

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Golbez posted:

Is there an easy way, then, to get the range for a particular row in one query?

This logically does what you want, I don't know if a better way exists.

code:

SELECT
    MyTable1.StartDate,
    MyTable2.StartDate AS EndDate
FROM
    MyTable AS MyTable1
    LEFT JOIN MyTable AS MyTable2 ON MyTable1.StartDate < MyTable2.StartDate
    LEFT JOIN MyTable AS MyTable3 ON
        MyTable1.StartDate < MyTable3.StartDate AND
        MyTable3.StartDate < MyTable2.StartDate
WHERE
    MyTable3.StartDate IS NULL

# ? Feb 7, 2013 22:23

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

So what you're saying is, since I'm on MySQL, I should include an end date. :v:

# ? Feb 7, 2013 22:32

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Golbez posted:

So what you're saying is, since I'm on MySQL, I should include an end date.

You could make it a view I guess. Whether you should make it an end date maybe depends how often you're going to require end dates.

It just occurred to me, if you do add an end date column you might be able to use foreign key constraints to make sure that the end date and start date data remain consistent. This might require a null start date for the first row. I haven't really thought this through.

# ? Feb 7, 2013 22:42

hewerman: Jun 5, 2008

Golbez posted:

So what you're saying is, since I'm on MySQL, I should include an end date.

Hammerite posted:

You could make it a view I guess. Whether you should make it an end date maybe depends how often you're going to require end dates.

It just occurred to me, if you do add an end date column you might be able to use foreign key constraints to make sure that the end date and start date data remain consistent. This might require a null start date for the first row. I haven't really thought this through.

Yeah, if you are worried about dates overlapping, enforce it with a constraint. But how often are you going to need to see the duration of a specific row? I know I brought this up initially, but my line of thinking was less around an audit trail and more around a dimension in a data warehouse. For an audit trail I would think just storing the time the change occurred would be all you need. If you really want to report on it, use a view and Hammmerite's query looks like it would do the trick.

hewerman fucked around with this message at 02:29 on Feb 8, 2013

# ? Feb 8, 2013 02:20

Chuu: Sep 11, 2004; Grimey Drawer

I've been messing around with using various sql databases to store a massive amount of time series data (100M-1B rows), essentially denormalized data where the first column is a timestamp, and that column is *sorted by time*.

I've noticed that some column stores optimize for this case already (It looks like they're keeping track of the range of values in each slice, so they can throw away massive amounts of data when I query over specific time periods) -- but for those that don't, is there any specific type of index that will help for queries of the form (SELECT * FROM timeSeriesTable ... WHERE timestamp < X AND timestamp > Y)? Most of the solutions are MySQL engines or forked from Postgres a long time ago.

# ? Feb 12, 2013 07:23

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Chuu posted:

I've been messing around with using various sql databases to store a massive amount of time series data (100M-1B rows), essentially denormalized data where the first column is a timestamp, and that column is *sorted by time*.

I've noticed that some column stores optimize for this case already (It looks like they're keeping track of the range of values in each slice, so they can throw away massive amounts of data when I query over specific time periods) -- but for those that don't, is there any specific type of index that will help for queries of the form (SELECT * FROM timeSeriesTable ... WHERE timestamp < X AND timestamp > Y)? Most of the solutions are MySQL engines or forked from Postgres a long time ago.

If it is available to you, clustered index on that column, or a set of columns including the timestamp comprising a unique key, will give you the biggest bang for your buck.

# ? Feb 12, 2013 16:11

epswing: Nov 4, 2003; Soiled Meat

SQL code:

-- Region Parameters
DECLARE @p0 Int = 123
DECLARE @p1 DateTime = '2013-02-14 00:00:00.000'
DECLARE @p2 DateTime = '2013-02-15 00:00:00.000'
-- EndRegion
SELECT 
    (CASE 
        WHEN EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [Shifts] AS [t0]
            WHERE ([t0].[DriverId] = @p0) AND ([t0].[PunchInTime] > @p1) AND ([t0].[PunchInTime] < @p2)
            ) THEN 1
        ELSE 0
     END) AS [value]

In SQL Server 2008 R2 Express, when I analyze the preceding query in SSMS it shows a Clustered Index Scan :ohdear:

. If I remove the ([t0].[DriverId] = @p0) it shows as an Index Seek :cool:

. After adding an index to DriverId and rerunning the original query, I would expect a Seek, not a Scan, but the analyzer still says it's a Scan.

Am I wrong in expecting this? I'm worried because this table is essentially a log that will grow forever, so eventually this query ("has this driver punched in today?") will get slow if it needs to scan the table every time.

# ? Feb 14, 2013 20:00

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

What is the definition of the clustered index/primary key?

Also, can SQL Express have multi-column indexes? If so, try creating one index that uses both DriverId and PunchInTime.

Nth Doctor posted:

[Definitions of clustered index/primary key]

This post was in response to epalm's problem above.

Jethro fucked around with this message at 16:01 on Feb 15, 2013

# ? Feb 14, 2013 23:45

Kilson: Jan 16, 2003; I EAT LITTLE CHILDREN FOR BREAKFAST !!11!!1!!!!111!

Using Postgres, I have a multi-table join, and I basically need to join one of the tables on a condition of non-existence, but that's not really how a join works, so I'm having trouble figuring out what to do.

I'll try to give a minimal example:

SQL code:

create table users (id serial primary key, name text);

create table place (id serial primary key, name text);

create table phone_number (
  id serial primary key,
  tn varchar(10) unique,
  user_id int references users(id) unique,
  place_id int references place(id));

create table device (
  id serial primary key,
  name text unique,
  place_id int references place(id));

create table device_phone_mapping (
  id serial primary key,
  device_id int references device(id),
  tn_id int references phone_number(id));

And here's some data:

code:

select * from user;
 id | name
----+------
  1 |  u1

select * from place
 id | name
----+------
  1 |  p1
  2 |  p2
  3 |  p3

select * from phone_number
 id |     tn     | user_id | place_id
----+------------+---------+----------
  1 | 5551234567 |   null  |       1
  2 | 5551002000 |      1  |       2
  3 | 5551002001 |   null  |       2

select * from device;
 id | name | place_id
----+------+----------
  1 |  d1  |        1
  2 |  d2  |        2
  3 |  d3  |        3

select * from device_phone_mapping;
 id | device_id | tn_id
----+-----------+-------
  1 |         3 |     2

SQL code:

create or replace view device_view as
	select
	device.id as id,
	device.name as device_name,
	place.name as place_name,
	phone_number.tn as tn
	from device
	inner join device_phone_mapping on (device.id in (select device_phone_mapping.device_id))
	left outer join phone_number on (device_phone_mapping.tn_id = phone_number.id)
	left outer join place on (phone_number.place_id = place.id);

This gives me any devices and phone numbers that are in the mapping table. I want to union this with exactly the set of devices not already selected, with their phone numbers selected by shared location (and the phone number doesn't have a user id). Any ideas?

# ? Feb 15, 2013 00:16

Pardot: Jul 25, 2001

Maybe try wrapping that query in a CTE?

code:

with devicewhatever as ( select device.id as id ...etc)
select * from devicewhatever
union all
select * from .... where id not in (select id from devicewhatever) ...

# ? Feb 15, 2013 00:40

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Jethro posted:

What is the definition of the clustered index/primary key?

Also, can SQL Express have multi-column indexes? If so, try creating one index that uses both DriverId and PunchInTime.

A clustered index defines the order in which the table is laid out. It's like the page number in a book. Nonclustered indices are like looking up an item in the index in the back of a book, giving a pointer to the page the information you are after.

A primary key is a column or set of columns that comprise a unique fingerprint for each row in the table.

# ? Feb 15, 2013 03:07

Beelzebozo: Nov 6, 2002; I don’t mean to sound bitter, cold, or cruel. But I am, so that’s how it comes out.

Kilson posted:

Using Postgres, I have a multi-table join, and I basically need to join one of the tables on a condition of non-existence, but that's not really how a join works, so I'm having trouble figuring out what to do.

CTEs won't help you here. What you're trying to do is called an anti-join, and in postgres these are expressed as a left outer join where the right side is NULL.

In your example, you probably want something like:

SQL code:

SELECT device.id AS id,
	device.name AS device_name,
	place.name AS place_name,
	phone_number.tn AS tn
FROM device
INNER JOIN device_phone_mapping ON (device.id IN (SELECT device_phone_mapping.device_id))
LEFT OUTER JOIN phone_number ON (device_phone_mapping.tn_id = phone_number.id)
LEFT OUTER JOIN place ON (phone_number.place_id = place.id)
UNION SELECT device.id AS id,
	device.name AS device_name,
	place.name AS place_name,
	phone_number.tn AS tn
FROM device
LEFT OUTER JOIN device_phone_mapping ON device_id = device.id
LEFT OUTER JOIN phone_number USING (place_id)
LEFT OUTER JOIN place on (phone_number.place_id = place.id)
WHERE device_phone_mapping.device_id IS NULL -- anti-join
ORDER BY id;

# ? Feb 15, 2013 14:08

Kilson: Jan 16, 2003; I EAT LITTLE CHILDREN FOR BREAKFAST !!11!!1!!!!111!

Beelzebozo posted:

CTEs won't help you here. What you're trying to do is called an anti-join, and in postgres these are expressed as a left outer join where the right side is NULL.

This gave me exactly the same results as the CTE, and they're both unfortunately wrong.

code:

 id | device_name | place_name |     tn     
----+-------------+------------+------------
  1 | d1          | p1         | 5551234567
  2 | d2          | p2         | 5551002000
  3 | d3          | p2         | 5551002000

The result should look like this:

code:

 id | device_name | place_name |     tn     
----+-------------+------------+------------
  1 | d1          | p1         | 5551234567
  2 | d2          | p2         | NULL
  3 | d3          | p2         | 5551002000

Because d2 isn't mapped specifically to a phone number, and the only phone number in the same location as d2 has a user, so it's not allowed to be used except when tied to the mapping (of d3, in this case).

A minor change fixed it though. In the second select, I just have to join phone_number on the conditions that the place_id matches AND the user_id is null.

Thanks guys!

# ? Feb 15, 2013 16:26

Beelzebozo: Nov 6, 2002; I don’t mean to sound bitter, cold, or cruel. But I am, so that’s how it comes out.

Kilson posted:

Because d2 isn't mapped specifically to a phone number, and the only phone number in the same location as d2 has a user, so it's not allowed to be used except when tied to the mapping (of d3, in this case).

Oh, yeah, if you'd typed that just like that it would've helped. I completely misunderstood your question. Glad you got it worked out though.

# ? Feb 15, 2013 17:20

Stealthgerbil: Dec 16, 2004

I set up a mysql express database and am trying to get it to work with wordpress. I keep getting an error for establishing a database connection and I feel like it is a permission issue with IIS. Anyone have any ideas? I can log into the sql database fine with the user name and password.

# ? Feb 15, 2013 21:49

RabbitWizard: Oct 21, 2008; Muldoon

Hi, i hope i'm at the right place for my question....it's stupid, so it should fit. How can i make this stuff shorter?
I just want to get the userid and username for all users. I also use this, even if i only want one user, then with "SELECT * FROM users WHERE userid='$userid'"

php:

<?
$sqlquery = "SELECT * FROM users ORDER BY username";
$reading = mysql_query($sqlquery);
while($row = mysql_fetch_object($reading))
{
$userid=$row->userid;
$username=$row->username;
echo somevariables;
}
?>

Please tell me how much of an idiot i am, thanks

I'm not afraid of reading, so i would be ok if someone could throw some links in my direction.

# ? Feb 15, 2013 22:08

Beelzebozo: Nov 6, 2002; I don’t mean to sound bitter, cold, or cruel. But I am, so that’s how it comes out.

RabbitWizard posted:

How can i make this stuff shorter?

Switch to a language that has less boilerplating than PHP. Not trolling. That's a sincere answer. What you have there is about as terse as PHP can go on that operation.

# ? Feb 15, 2013 22:09

glompix: Jan 19, 2004; propane grill-pilled

Beelzebozo posted:

Switch to a language that has less boilerplating than PHP. Not trolling. That's a sincere answer. What you have there is about as terse as PHP can go on that operation.

To be fair, he could use an ORM too. PHP sucks pretty hard but it's used drat near everywhere and surely there are some smart people dealing with it. I'm not familiar with the frameworks available for PHP, though, but I bet there's something like Hibernate or SQLAlchemy available. Maybe search for those.

As for a specific thing that may help improve code quality, try selecting only the UserId and UserName like "select username, userid from users order by username". I don't know how wide your Users table is, but it's more optimal to select only what you need in a query instead of *. Just less data to transfer over pipes or a network connection or what have you. If your method is re-used a lot, you might not be able to avoid it, of course.

# ? Feb 15, 2013 22:39

RabbitWizard: Oct 21, 2008; Muldoon

Thanks for your answer Beelzebozo, the stuff i understood (1/4 of it) was kinda funny, but i really don't want to learn another language because i know what i want to do works with php.
It won't be a page open to the public, so i don't have to deal with stupid inputs or sql-injections.
Maybe there is a chance no one will ever see my code, so i can program as lovely as i want. Even if someone saw it, i would have made my money by then.
It's just for myself, i don't want to have 3 lines if it could be one because.......well, because.

Anyway, if i ever should sell my PoS, you get :10bux:

from me for answering. Could take a while, but i noted your username

(Yeah, chances are (very) slim, but i think i could sell it)

ninjaedit: :10bux:

for glompix as well, if i ever sell it

# ? Feb 15, 2013 22:43

karms: Jan 22, 2006; by Nyc_Tattoo; Yam Slacker

RabbitWizard posted:

Thanks for your answer Beelzebozo, the stuff i understood (1/4 of it) was kinda funny, but i really don't want to learn another language because i know what i want to do works with php.
It won't be a page open to the public, so i don't have to deal with stupid inputs or sql-injections.
Maybe there is a chance no one will ever see my code, so i can program as lovely as i want. Even if someone saw it, i would have made my money by then.
It's just for myself, i don't want to have 3 lines if it could be one because.......well, because.

Anyway, if i ever should sell my PoS, you get from me for answering. Could take a while, but i noted your username (Yeah, chances are (very) slim, but i think i could sell it)

ninjaedit: for glompix as well, if i ever sell it

I wonder who's the sucker lining up to buy your crap.

# ? Feb 15, 2013 23:04

RoadCrewWorker: Nov 19, 2007; camels aren't so great

RabbitWizard posted:

i can program as lovely as i want. Even if someone saw it, i would have made my money by then.

Make sure to put this on your resume.

gently caress, just make it your resume because.......well, because.

# ? Feb 15, 2013 23:18

RabbitWizard: Oct 21, 2008; Muldoon

Well, thats easy....at thist company, i wanted to make a small database for all the stuff we need.....instead i got fired (because i was sick and didn't want to show up sick in a company that sells food-items). So the company asked a "real" company to do their database. 10k for analysing their needs(which they paid, and then didn't take the offer), 100k for the the finished product. Instead of me taking a 2k course to learn the software and 5k for 1 licence.
So i thought about writing my own software for them. Even if it takes 4 weeks, if i can sell it to those motherfuckers, it would be worth it.
I know what they need, and i can give it to them. Of course a relative of mine would sell it to those assholes, not me. So it could work.
Yes, i'm still bad with php, but you won't believe the poo poo they bought over the years i was there. What i learned is that if it doesnt cost much, it can't be worth anything. And i think i have a good chance of loving them over.
(Germany!!!!!!!!)

# ? Feb 15, 2013 23:19

glompix: Jan 19, 2004; propane grill-pilled

RabbitWizard posted:

Well, thats easy....at thist company, i wanted to make a small database for all the stuff we need.....instead i got fired (because i was sick and didn't want to show up sick in a company that sells food-items). So the company asked a "real" company to do their database. 10k for analysing their needs(which they paid, and then didn't take the offer), 100k for the the finished product. Instead of me taking a 2k course to learn the software and 5k for 1 licence.
So i thought about writing my own software for them. Even if it takes 4 weeks, if i can sell it to those motherfuckers, it would be worth it.
I know what they need, and i can give it to them. Of course a relative of mine would sell it to those assholes, not me. So it could work.
Yes, i'm still bad with php, but you won't believe the poo poo they bought over the years i was there. What i learned is that if it doesnt cost much, it can't be worth anything. And i think i have a good chance of loving them over.
(Germany!!!!!!!!)

Let us know how this goes! :allears:

# ? Feb 15, 2013 23:48

RabbitWizard: Oct 21, 2008; Muldoon

I sure will! SHSC not believing in me gives me affirmation i never needed, but like i said, theres a small chance it will work

At least i know i posted in the right thread. Thanks glompix for your answer. I will be silent now until i make my big bucks.

# ? Feb 15, 2013 23:56

Sab669: Sep 24, 2009

Dumb question, but is there an easier way to search based on a single column with multiple values?

ie.
"SELECT * FROM Table WHERE PK = 1,2,3" instead of:
"SELECT * FROM Table WHERE PK = 1 OR PK = 2 OR PK = 3"

The latter is just so much uglier...

# ? Feb 21, 2013 15:58

Bad Munki: Nov 4, 2008; We're all mad here.

You're looking for the IN operator:

code:

SELECT * FROM Table WHERE PK IN (1, 2, 3)

# ? Feb 21, 2013 16:02

Sab669: Sep 24, 2009

Oh, hm, I guess I knew that. Just haven't used it in a long time. Thanks

# ? Feb 21, 2013 16:08

Opulent Ceremony: Feb 22, 2012

I am not a DBA, so I was wondering if anyone out there could clear up any of my thoughts regarding a current issue at work. We are using SQL Server 2008 R2 (I think) Enterprise edition.

To simplify the problem, we have these two databases with the same schema, A and B, that are both on the same server, and we want to keep their data in sync with each other. Actually, we just want to make sure that whatever data is in A also gets put into B in a sort-of timely fashion. If inserts happen in B, A doesn't need to know about it. Basically, B is the one that should have the full set of data.

B is a production database that people will potentially be querying and inserting into 24/7.

A only exists because there is a secondary way to insert data into our system, and in a round-about way ends up in A (migrated via mapping scripts from a bizarre database on our unix system).

This is all still in the design phase for at least a little longer and I've only now become a part of examining it, and their current plan seems odd to me: they've designed B to have 2 sets of each table from A, and a view for each set of tables. Someone wrote a merge script to push data from A to B into one set of tables, the other set being left open so B can still be queried via the view, which will be hooked up to the non-merging tables (the assumption being the merge script locks the tables on B). The script alternates which table set it merges into so that both sets get the new data from A eventually. I think the primary keys are kept distinct by a separate column on each table identifying its source, which becomes a composite with the traditional ID column.

This all seems like a weird work-around to me, and from my very preliminary googling it seems like we can maybe use Snapshot Replication from A to B every so often to accomplish this same task (I think without the convoluted scripts) and it would also not lock the tables in B, who would be the subscriber.

Does anyone more knowledgable have any advice to offer in my situation? Are either of these ideas viable? Is something terribly dumb?

Thank you for reading about my problems.

# ? Feb 22, 2013 17:28

glompix: Jan 19, 2004; propane grill-pilled

Opulent Ceremony posted:

I am not a DBA, so I was wondering if anyone out there could clear up any of my thoughts regarding a current issue at work. We are using SQL Server 2008 R2 (I think) Enterprise edition.

To simplify the problem, we have these two databases with the same schema, A and B, that are both on the same server, and we want to keep their data in sync with each other. Actually, we just want to make sure that whatever data is in A also gets put into B in a sort-of timely fashion. If inserts happen in B, A doesn't need to know about it. Basically, B is the one that should have the full set of data.

B is a production database that people will potentially be querying and inserting into 24/7.

A only exists because there is a secondary way to insert data into our system, and in a round-about way ends up in A (migrated via mapping scripts from a bizarre database on our unix system).

This is all still in the design phase for at least a little longer and I've only now become a part of examining it, and their current plan seems odd to me: they've designed B to have 2 sets of each table from A, and a view for each set of tables. Someone wrote a merge script to push data from A to B into one set of tables, the other set being left open so B can still be queried via the view, which will be hooked up to the non-merging tables (the assumption being the merge script locks the tables on B). The script alternates which table set it merges into so that both sets get the new data from A eventually. I think the primary keys are kept distinct by a separate column on each table identifying its source, which becomes a composite with the traditional ID column.

This all seems like a weird work-around to me, and from my very preliminary googling it seems like we can maybe use Snapshot Replication from A to B every so often to accomplish this same task (I think without the convoluted scripts) and it would also not lock the tables in B, who would be the subscriber.

Does anyone more knowledgable have any advice to offer in my situation? Are either of these ideas viable? Is something terribly dumb?

Thank you for reading about my problems.

I don't claim to understand the convoluted data dance going on here with the multiple tables/views at all, but an issue you'll run into with replication technologies are primary key collisions. I believe all replication types (certainly transactional and merge, probably snapshot) try to make the primary keys match up to the source. If you're not using natural keys everywhere, you'll hit collisions. It's not impossible to deal with (segment your key ranges somehow is the easiest way) but it's something to think about.

A dead-loving-simple extract/transform/load solution would probably be the only way to go for this, and SQL Server Integration Services might be able to help you with it. I don't know much about it.

Also, if you're worried about locking... are you using snapshot isolation?

# ? Feb 22, 2013 18:13

Opulent Ceremony: Feb 22, 2012

Thanks for the thoughts. Regarding Snapshot Isolation, I don't believe it is currently being used and was unaware of this feature, thanks!

# ? Feb 22, 2013 19:50

Jo: Jan 24, 2005; Soiled Meat

Is this thread open to NoSQL questions as well? If so, I have a collection of usernames which own a resource. I could put them as an array inside of a document in the collection, or, alternatively, I could make each of the usernames in the array a single document. Are there any general design policies one way or another? The query method would likely be single 'is foo in this set' type operations.

# ? Feb 22, 2013 21:39

Adbot: ADBOT LOVES YOU

# ? May 30, 2024 11:34

Hot Yellow KoolAid: Aug 17, 2012

I'm having trouble writing a MySQL query for a query for displaying a college courses prerequisite courses. Each course only has at most a single prerequisite, with some courses having none. The query requires two tables, a course and prerequisite table. They have the following schema:

`course` (`course_id`, `title`, `dept_name`, `credits`)
`prereq` (`course_id`, `prereq_id`)

I wrote a crude and obvious query using natural join, but I've been stumped since...

code:

select course.title, prereq_id
from course natural join prereq;

This solution has created two problems:

1.) Some of the courses don't have prerequisites. For these results in the query, the `prereq` column should say "None" or "N/A". In it's current form, this query completely omits courses without prerequistes.

2.) `course_id` and `prereq_id` are both varchars in the form of XXX-XXX. The query result needs to include the course `title` and prereq `title`, also varchars (note that `title` is not on prereq's schema). I have been experimenting with both temporary tables and nested queries, but I can't get the prerequisite column to display anything but the `prereq_id` instead of an equivalent `title`.

# ? Feb 25, 2013 01:00

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »